﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using BoutiquePOS.DAL.DAO;


namespace BoutiquePOS.DAL.Gateway
{
    public class ShelfGateway : DbGateway
    {
        public string SaveShelf(Shelf aShelf)
        {
            try
            {
                ConnectionObj.Open();
                string query = String.Format("INSERT INTO PosShelf VALUES( @shelfName)");
                CommandObj.Parameters.Clear();
                CommandObj.CommandText = query;
                CommandObj.Parameters.Add("@shelfName", SqlDbType.VarChar);
                CommandObj.Parameters["@shelfName"].Value = aShelf.ShelfName;
                CommandObj.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception("Company couldn't saved", ex);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            return "Save successfully";
        }

        public List<Shelf> GetAllShelf()
        {
            List<Shelf> shelfs = new List<Shelf>();
            try
            {
                ConnectionObj.Open();
                string query = String.Format("SELECT * FROM PosShelf");

                CommandObj.CommandText = query;
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    Shelf aShelf = new Shelf();
                    aShelf.ShelfId = Convert.ToInt32(reader["ShelfId"]);
                    aShelf.ShelfName = reader["ShelfName"].ToString();
                    shelfs.Add(aShelf);
                }
            }
            catch (Exception exception)
            {
                throw new Exception("Error occurred during Form loading from your system.", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            return shelfs;
        }

        public string DeleteShelfById(int shelfId)
        {
            int i = 0;
            try
            {
                ConnectionObj.Open();
                string delete = String.Format("DELETE FROM PosShelf WHERE shelfId='{0}'", shelfId);
                CommandObj.CommandText = delete;
                i = CommandObj.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw new Exception("Database Error");
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            if (i != 0)
            {
                return "Delete Successfully";
            }
            else
            {
                return "Shelf Id does not found";
            }
        }

        public List<Shelf> GetAllShelfByCodeOrName(int i, string shelfId)
        {
            List<Shelf> aShelf = new List<Shelf>();
            string select = "";

            try
            {
                ConnectionObj.Open();
                if (i == 0)
                {

                    select = String.Format("SELECT * FROM PosShelf WHERE shelfId ='{0}'", Convert.ToInt32(shelfId));
                }
                if (i == 1)
                {
                    shelfId = shelfId + "%";
                    select = String.Format("SELECT * FROM PosShelf WHERE shelfName LIKE '" + shelfId + "'");
                }

                CommandObj.CommandText = select;
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    Shelf shelf = new Shelf();
                    shelf.ShelfName = reader[1].ToString();
                    shelf.ShelfId = Convert.ToInt32(reader[0]);
                    aShelf.Add(shelf);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            return aShelf;
        }



    }
}